package com.css.fxfzysx.util;


import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.*;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @ Description: 读取excel工具类
 */
@Slf4j
public class ExcelReaderUtil {
    /**
     * Excel读取 操作,返回内容
     */
    public static List<List<String>> readExcel(InputStream is, int index, int sheetIndex)
            throws IOException {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        /** 得到第一个sheet */
        Sheet sheet = wb.getSheetAt(sheetIndex);
        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();
        /** 得到Excel的列数 */
        int totalCells = 0;
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<List<String>> dataLst = new ArrayList<List<String>>();
        /** 循环Excel的行 */
        for (int r = index; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;
            List<String> rowLst = new ArrayList<String>();
            /** 循环Excel的列 */
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
                    HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                    cellValue = hSSFDataFormatter.formatCellValue(cell);
                    if (cellValue.contains("/")) {
                        if (cellValue.split("/").length == 3) {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                            double value = cell.getNumericCellValue();
                            Date date = DateUtil
                                    .getJavaDate(value);
                            cellValue = sdf.format(date);
                        }
                    }

                }
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            if ("".equals(rowLst.get(0)) && "".equals(rowLst.get(1)) && "".equals(rowLst.get(2))) {

            } else {
                dataLst.add(rowLst);
            }
        }
        return dataLst;
    }

    public static Boolean checkTitle(InputStream is, int index, int sheetIndex, String title) throws Exception {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        /** 得到第一个sheet */
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = sheet.getRow(index);
        if (PlatformObjectUtils.isEmpty(row)) {
            throw new Exception("第" + sheetIndex + "sheet页的表头为空，请检查模板");
        }
        //得到所有的列
        int totleCelNum = row.getPhysicalNumberOfCells();
        String resultValue = "";
        for (int i = 0; i < totleCelNum; i++) {
            Cell cell = row.getCell(i);
            if(cell==null || "".equals(cell.getStringCellValue()) || ObjectUtils.isEmpty(cell)){
                continue;
            }
            HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
            String cellValue = hSSFDataFormatter.formatCellValue(cell);
            if (i == 0) {
                resultValue = resultValue + cellValue;
            } else {
                resultValue = resultValue + "," + cellValue;
            }
        }
        if (PlatformObjectUtils.isNotEmpty(resultValue) && resultValue.equals(title)) {
            return true;
        } else {
            throw new Exception("第" + sheetIndex + "sheet页的表头为空，请检查模板");
        }
    }

    /**
     * @methodName: readExcel 读取excel工具类
     * @param: [is, tClass]  传入的实体类,成员变量类型只能是基本类型和字符串
     * @return: java.util.List<T>
     * @Description: 读取excel文件, 将其转换为javabean
     */
    public static <T> ExcelResult readExcelBean(InputStream is, Class<T> tClass, int index, int sheetIndex)
            throws Exception {
        List<List<String>> list = ExcelReaderUtil.readExcel(is, index, sheetIndex);
        //-----------------------遍历数据到实体集合开始-----------------------------------
        List<String> bardlist = new ArrayList<>();//存入错误提示的list
        List<T> listBean = new ArrayList<T>();
        Field[] fields = tClass.getDeclaredFields();
        List<Field> fields2 = new ArrayList<>();
        for (Field field : fields) {
            if (field.getAnnotation(ExcelPropertyCheck.class) != null) {
                fields2.add(field);
            }
        }
        fields2.sort((o1, o2) -> {
            Integer index1 = o1.getAnnotation(ExcelPropertyCheck.class).index();
            Integer index2 = o2.getAnnotation(ExcelPropertyCheck.class).index();
            return index1.compareTo(index2);
        });
        fields = fields2.toArray(new Field[fields2.size()]);
        T uBean = null;
        for (int i = 0; i < list.size(); i++) {// i=1表示第一行不要,i=0表示要表头
            uBean = (T) tClass.newInstance();
            List<String> listStr = list.get(i);
            int a = 0;
            for (int j = 0; j < listStr.size(); j++) {
                Field field = fields[j];
                field.setAccessible(true);
                ExcelPropertyCheck annotation = field.getAnnotation(ExcelPropertyCheck.class);
                String datastring = listStr.get(j);
//                if (datastring.length() == 0 && annotation.required()) {
//                    throw new Exception("第" + (i + 1) + "行列名称为:" + annotation.name() + " 存在空值");
//                   /* bardlist.add("第" + (i + 1) + "行列名称为:" + annotation.name() + " 存在空值");
//                    a++;
//                    continue;*/
//                }
                if (datastring.length() > 0) {
                    if (-1 != annotation.maxLength() && annotation.maxLength() < datastring.length()) {
                        throw new Exception("第" + (i + 1) + "行列名称为:" + annotation.name() + " 字数超长,规定长度为:" + annotation.maxLength());
                        /*bardlist.add();
                        a++;
                        continue;*/
                    }
                    if (-1 != annotation.length() && annotation.length() != datastring.length()) {
                        throw new Exception("第" + (i + 1) + "行列名称为:" + annotation.name() + " 字数长度与规定长度不符,规定长度为:" + annotation.length());
                      /*  bardlist.add();
                        a++;
                        continue;*/
                    }
                    Class<?> type = field.getType();
                    // 只支持8中基本类型和String类型 如有其他类型 请自行添加
                    try {
                        if (type == String.class) {
                            field.set(uBean, datastring);
                        } else if (type == Integer.class || type == int.class) {
                            field.set(uBean, Integer.parseInt(datastring));
                        } else if (type == Double.class || type == double.class) {
                            field.set(uBean, Double.parseDouble(datastring));
                        } else if (type == Float.class || type == float.class) {
                            field.set(uBean, Float.parseFloat(datastring));
                        } else if (type == Long.class || type == long.class) {
                            field.set(uBean, Long.parseLong(datastring));
                        } else if (type == Boolean.class || type == boolean.class) {
                            field.set(uBean, Boolean.parseBoolean(datastring));
                        } else if (type == Short.class || type == short.class) {
                            field.set(uBean, Short.parseShort(datastring));
                        } else if (type == Byte.class || type == byte.class) {
                            field.set(uBean, Byte.parseByte(datastring));
                        } else if (type == Character.class || type == char.class) {
                            field.set(uBean, datastring.charAt(0));
                        } else if (type == Date.class) {
                            try {
                                SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
                                Date parse = format.parse(datastring);
                                field.set(uBean, parse);
                            } catch (Exception e) {
                                throw new Exception("第" + (i + 1) + "行列名称为:" + annotation.name() + " 类型错误. 例:(2020/12/20)");
                               /* log.info("第" + (i + 1) + "行列名称为:" + annotation.name() + " 类型错误. 例:(2020/12/20)");
                                bardlist.add("第" + (i + 1) + "行列名称为:" + annotation.name() + " 类型错误. 例:(2020/12/20)");
                                a++;
                                continue;*/
                            }
                        }
                    } catch (Exception e) {
                        throw new Exception("第" + (i + 1) + "行列名称为:" + annotation.name() + " 类型错误,规定类型为:" + type.getName().substring(type.getName().lastIndexOf(".") + 1));
                      /*  log.info("第" + (i + 1) + "行列名称为:" + annotation.name() + " 类型错误,规定类型为:" + type.getName().substring(type.getName().lastIndexOf(".") + 1));
                        bardlist.add("第" + (i + 1) + "行列名称为:" + annotation.name() + " 类型错误,规定类型为:" + type.getName().substring(type.getName().lastIndexOf(".") + 1));
                        a++;
                        continue;*/
                    }
                }
            }
            if (a == 0) {
                listBean.add(uBean);
            }
        }
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("成功导入" + listBean.size() + "条数据，" + "失败条数为" + (list.size() - 1 - listBean.size()) + ".").append("<br/>");
        if (bardlist.size() != 0) {
            for (String s : bardlist) {
                stringBuilder.append(s).append("<br/>");
            }
        }
        ExcelResult excelResult = new ExcelResult();
        excelResult.setData(listBean);
        excelResult.setMessage(stringBuilder.toString());
        return excelResult;
    }


    public static <T> List<T> readExcel2Bean(InputStream is, Class<T> tClass)
            throws IOException, IllegalAccessException, InstantiationException {
        List<List<String>> list = ExcelReaderUtil.readExcel(is, 0, 0);
        //-----------------------遍历数据到实体集合开始-----------------------------------
        List<T> listBean = new ArrayList<T>();
        Field[] fields = tClass.getDeclaredFields();
        T uBean = null;
        for (int i = 1; i < list.size(); i++) {// i=1表示第一行不要,i=0表示要表头
            uBean = (T) tClass.newInstance();
            List<String> listStr = list.get(i);
            for (int j = 0; j < listStr.size(); j++) {
                if (j >= fields.length) {
                    break;
                }
                Field field = fields[j];
                String datastring = listStr.get(j);
                field.setAccessible(true);
                if (datastring.length() > 0 && datastring != null) {
                    Class<?> type = field.getType();
                    // 只支持8中基本类型和String类型 如有其他类型 请自行添加
                    if (type == String.class) {
                        field.set(uBean, datastring);
                    } else if (type == Integer.class || type == int.class) {
                        field.set(uBean, Integer.parseInt(datastring));
                    } else if (type == Double.class || type == double.class) {
                        field.set(uBean, Double.parseDouble(datastring));
                    } else if (type == Float.class || type == float.class) {
                        field.set(uBean, Float.parseFloat(datastring));
                    } else if (type == Long.class || type == long.class) {
                        field.set(uBean, Long.parseLong(datastring));
                    } else if (type == Boolean.class || type == boolean.class) {
                        field.set(uBean, Boolean.parseBoolean(datastring));
                    } else if (type == Short.class || type == short.class) {
                        field.set(uBean, Short.parseShort(datastring));
                    } else if (type == Byte.class || type == byte.class) {
                        field.set(uBean, Byte.parseByte(datastring));
                    } else if (type == Character.class || type == char.class) {
                        field.set(uBean, datastring.charAt(0));
                    }
                }
            }
            listBean.add(uBean);
        }
        return listBean;
    }
}


